/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import java.util.ArrayList;
import wargame.entity.*;
import connectionmanager.DB;

/**
 *
 * @author stanley.2007
 */
public class ProjectDAO {

    public static Project retrieveProject(int project_id) {
        String sql = "SELECT `project_id`, `man_hours_dot_net`, `man_hours_c_sharp`,"
                + " `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, `man_hours_mysql`,"
                + " `man_hours_mssql`, `man_hours_oracle`, `max_bid`, `listing_turn_remaining`,"
                + " `due_turn`, `offered_by`, `taken_by`, `bidded_price`, `submitted`, `track_record_point`"
                + " FROM `project` WHERE project_id = " + project_id;
        DB db = new DB();
        db.connect();
        db.query(sql);
        Project p = null;
        if (db.next()) {
            p = populate(db);
        }
        db.close();
        return p;
    }

    public static Project populate(DB db) {
        Project p = new Project();
        p.setProject_id(db.getInt("project_id"));
        p.setMan_hours_dot_net(db.getInt("man_hours_dot_net"));
        p.setMan_hours_c_sharp(db.getInt("man_hours_c_sharp"));
        p.setMan_hours_jsp(db.getInt("man_hours_jsp"));
        p.setMan_hours_php(db.getInt("man_hours_php"));
        p.setMan_hours_ruby(db.getInt("man_hours_ruby"));
        p.setMan_hours_mysql(db.getInt("man_hours_mysql"));
        p.setMan_hours_mssql(db.getInt("man_hours_mssql"));
        p.setMan_hours_oracle(db.getInt("man_hours_oracle"));
        p.setMax_bid(db.getInt("max_bid"));
        p.setListing_turn_remaining(db.getInt("listing_turn_remaining"));
        p.setDue_turn(db.getInt("due_turn"));
        p.setOffered_by(db.getInt("offered_by"));
        p.setTaken_by(db.getInt("taken_by"));
        p.setBidded_price(db.getInt("bidded_price"));
        p.setSubmitted(db.getInt("submitted"));
        p.setTrack_record_point(db.getInt("track_record_point"));
        Company company = CompanyDAO.retrieveCompany(p.getOffered_by());
        p.setCompany(company);
        return p;
    }

    public static int insertProject(Project p) {
        String sql = "INSERT INTO project (`man_hours_dot_net`, `man_hours_c_sharp`, "
                + "`man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, `man_hours_mysql`, "
                + "`man_hours_mssql`, `man_hours_oracle`, `max_bid`, `listing_turn_remaining`, "
                + "`due_turn`, `offered_by`, `taken_by`, `bidded_price`, `submitted`, "
                + "`track_record_point`) VALUES ('"
                + p.getMan_hours_dot_net() + "', '" + p.getMan_hours_c_sharp() + "', '"
                + p.getMan_hours_jsp() + "', '" + p.getMan_hours_php() + "', '"
                + p.getMan_hours_ruby() + "', '" + p.getMan_hours_mysql() + "', '"
                + p.getMan_hours_mssql() + "', '" + p.getMan_hours_oracle() + "', '"
                + p.getMax_bid() + "', '" + p.getListing_turn_remaining() + "', '"
                + p.getDue_turn() + "', '" + p.getOffered_by() + "', '"
                + p.getTaken_by() + "', '" + p.getBidded_price() + "', '"
                + p.getSubmitted() + "', '" + p.getTrack_record_point() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateProject(Project p) {
        String sql = "UPDATE project SET"
                + " `man_hours_dot_net` = '" + p.getMan_hours_dot_net()
                + "', `man_hours_c_sharp` = '" + p.getMan_hours_c_sharp()
                + "', `man_hours_jsp` = '" + p.getMan_hours_jsp()
                + "', `man_hours_php` = '" + p.getMan_hours_php()
                + "', `man_hours_ruby` = '" + p.getMan_hours_ruby()
                + "', `man_hours_mysql` = '" + p.getMan_hours_mysql()
                + "', `man_hours_mssql` = '" + p.getMan_hours_mssql()
                + "', `man_hours_oracle` = '" + p.getMan_hours_oracle()
                + "', `max_bid` = '" + p.getMax_bid()
                + "', `listing_turn_remaining` = '" + p.getListing_turn_remaining()
                + "', `due_turn` = '" + p.getDue_turn()
                + "', `offered_by` = '" + p.getOffered_by()
                + "', `taken_by` = '" + p.getTaken_by()
                + "', `bidded_price` = '" + p.getBidded_price()
                + "', `submitted` = '" + p.getSubmitted()
                + "', `track_record_point` = '" + p.getTrack_record_point()
                + "' WHERE project_id = '" + p.getProject_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void deleteProject(int project_id) {
        String sql = "DELETE FROM project WHERE project_id = '" + project_id + "'";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void updateProjectStatus(Project p) {
        String sql = "UPDATE project SET `submitted` = '" + p.getSubmitted()
                + "' WHERE project_id = '" + p.getProject_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static ArrayList<Project> retrieveAllBidableProjects() {
        ArrayList<Project> projects = new ArrayList<Project>();
        String sql = "SELECT * FROM `project` WHERE `listing_turn_remaining` > 0";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            Project p = populate(db);
            projects.add(p);
        }
        return projects;
    }

    public static ArrayList<Project> retrievePlayerProjects(int player_id) {
        ArrayList<Project> projects = new ArrayList<Project>();
        String sql = "SELECT * FROM `project` WHERE taken_by =" + player_id + " AND submitted=0";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            Project p = populate(db);
            projects.add(p);
        }
        return projects;
    }

    public static ArrayList<Project> retrieveAllIncompleteProjectsOfPlayer(int player_id) {
        ArrayList<Project> projects = new ArrayList<Project>();
        String sql = "SELECT `project_id`, `man_hours_dot_net`, `man_hours_c_sharp`,"
                + " `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, `man_hours_mysql`,"
                + " `man_hours_mssql`, `man_hours_oracle`, `max_bid`, `listing_turn_remaining`,"
                + " `due_turn`, `offered_by`, `taken_by`, `bidded_price`, `submitted`, `track_record_point`"
                + " FROM `project` WHERE `taken_by` = '" + player_id + "' AND `submitted` = 0";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            //Project p = ProjectDAO.retrieveProject(db.getInt("project_id"));
            Project p = populate(db);
            projects.add(p);
        }
        return projects;
    }

    public static ArrayList<Project> retrieveAllProjects() {
        ArrayList<Project> projects = new ArrayList<Project>();
        String sql = "SELECT `project_id`, `man_hours_dot_net`, `man_hours_c_sharp`,"
                + " `man_hours_jsp`, `man_hours_php`, `man_hours_ruby`, `man_hours_mysql`,"
                + " `man_hours_mssql`, `man_hours_oracle`, `max_bid`, `listing_turn_remaining`,"
                + " `due_turn`, `offered_by`, `taken_by`, `bidded_price`, `submitted`, `track_record_point`"
                + " FROM `project`";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            //Project p = ProjectDAO.retrieveProject(db.getInt("project_id"));
            Project p = populate(db);
            projects.add(p);
        }
        return projects;

    }

    public static Player retrievePlayer(int projectID){
        int playerID=0;

        String sql = "SELECT `taken_by` from project where project_id='"+projectID+"'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            playerID=db.getInt("taken_by");
        }

        Player p=PlayerDAO.retrievePlayer(playerID);
        return p;
    }

    public static int totalHours(int projectID,int employeeID){
        int totalHour=0;

        String sql = "SELECT hour from assign_project where project_id="+projectID+" AND employee_id="+employeeID;
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            totalHour+=db.getInt("hour");
        }

        return totalHour;
    }

    public static void clear() {
        String sql = "TRUNCATE TABLE project";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear_assign_project() {
        String sql = "TRUNCATE TABLE assign_project";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }
}
